1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmCustomersRecord1 : Form
14 {
15
16 DataTable dtable = new DataTable();
17 SqlConnection con = null;
18 DataSet ds = new DataSet();
19 SqlCommand cmd = null;
20 DataTable dt = new DataTable();
21 ConnectionString cs = new ConnectionString();
22 public frmCustomersRecord1()
23 {
24 InitializeComponent();
25 }
26 public void GetData()
27 {
28 try{
29 con = new SqlConnection(cs.DBConn);
30 con.Open();
31 cmd = new SqlCommand( "SELECT RTRIM(CustomerID)as [Customer ID],RTRIM(Customername) as [Customer Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Customer order by CustomerName", con);
32 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
33 DataSet myDataSet = new DataSet();
34 myDA.Fill(myDataSet, "Customer");
35 dataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
36 con.Close();
37 }
38 catch (Exception ex)
39 {
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
41 }
42 }
43
44 private void frmCustomersRecord_Load(object sender, EventArgs e)
45 {
46 GetData();
47 }
48
49
50
51 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
52 {
53 string strRowNumber = (e.RowIndex + 1).ToString();
54 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
55 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
56 {
57 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
58 }
59 Brush b = SystemBrushes.ControlText;
60 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
61
62 }
63
64 private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
65 {
66 try{
67 DataGridViewRow dr = dataGridView1.SelectedRows[0];
68 this.Hide();
69 frmSales frm= new frmSales();
70 frm.Show();
71 frm.txtCustomerID.Text = dr.Cells[0].Value.ToString();
72 frm.txtCustomerName.Text = dr.Cells[1].Value.ToString();
73 frm.lblUser.Text = lblUser.Text;
74 frm.lblUserType.Text = lblUserType.Text;
75
76 }
77
78 catch (Exception ex)
79 {
80 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
81 }
82 }
83
84 private void txtCustomers_TextChanged(object sender, EventArgs e)
85 {
86 try
87 {
88 con = new SqlConnection(cs.DBConn);
89 con.Open();
90 cmd = new SqlCommand("SELECT RTRIM(CustomerID)as [Customer ID],RTRIM(Customername) as [Customer Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Customer where CustomerName like '" + txtCustomers.Text + "%' order by CustomerName", con);
91 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
92 DataSet myDataSet = new DataSet();
93 myDA.Fill(myDataSet, "Customer");
94 dataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
95 con.Close();
96 }
97 catch (Exception ex)
98 {
99 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
100 }
101 }
102
103 private void Button3_Click(object sender, EventArgs e)
104 {
105 if (dataGridView1.DataSource == null)
106 {
107 MessageBox.Show("Sorry nothing to export into excel sheet..", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
108 return;
109 }
110 int rowsTotal = 0;
111 int colsTotal = 0;
112 int I = 0;
113 int j = 0;
114 int iC = 0;
115 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
116 Excel.Application xlApp = new Excel.Application();
117
118 try
119 {
120 Excel.Workbook excelBook = xlApp.Workbooks.Add();
121 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
122 xlApp.Visible = true;
123
124 rowsTotal = dataGridView1.RowCount;
125 colsTotal = dataGridView1.Columns.Count - 1;
126 var _with1 = excelWorksheet;
127 _with1.Cells.Select();
128 _with1.Cells.Delete();
129 for (iC = 0; iC <= colsTotal; iC++)
130 {
131 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
132 }
133 for (I = 0; I <= rowsTotal - 1; I++)
134 {
135 for (j = 0; j <= colsTotal; j++)
136 {
137 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
138 }
139 }
140 _with1.Rows["1:1"].Font.FontStyle = "Bold";
141 _with1.Rows["1:1"].Font.Size = 12;
142
143 _with1.Cells.Columns.AutoFit();
144 _with1.Cells.Select();
145 _with1.Cells.EntireColumn.AutoFit();
146 _with1.Cells[1, 1].Select();
147 }
148 catch (Exception ex)
149 {
150 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
151 }
152 finally
153 {
154 //RELEASE ALLOACTED RESOURCES
155 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
156 xlApp = null;
157 }
158 }
159
160 private void frmCustomersRecord1_FormClosing(object sender, FormClosingEventArgs e)
161 {
162 this.Hide();
163 frmSales frm = new frmSales();
164 frm.lblUser.Text = lblUser.Text;
165 frm.lblUserType.Text = lblUserType.Text;
166 frm.Show();
167 }
168 }
169 }
